﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Modellayer;
using System.Data.SqlClient;
using System.Data;
using System.ServiceModel;

namespace Dblayer
{
    public class DbSeat
    {
        private SqlCommand cmd = null;

        public DbSeat()
        {

        }

        public void createSeats(Show show)
        {
            try
            {
                Connection.dbconn.Open();
                foreach (Seat seat in show.seats)
                {
                    string insert = @"insert into seat(SeatNumber, ShowId, Status) values ('" + seat.seatNumber + "','" + show.showId + "','" + seat.status + "')";
                    cmd = Connection.GetDBCommand(insert);
                    cmd.ExecuteNonQuery();
                    
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl: " + e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }

        public List<Seat> getAllSeats(int showId)
        {
            string sql = "SELECT * from seat WHERE showid = '" + showId + "'";
            return BuildSeats(sql);
        }

        public List<Seat> findSeatWithReservationId(int reservationid)
        {
            string sql = "select * from seat where reservationid = '" + reservationid + "'";
            return BuildSeats(sql);
        }


        private List<Seat> BuildSeats(string sql)
        {
            IDataReader dbReader;
            List<Seat> seats = new List<Seat>();

            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    Seat seat = new Seat((int)dbReader["seatnumber"], (int)dbReader["showid"]);
                    seat.status = int.Parse(dbReader["status"].ToString());
                    if (dbReader["ReservationId"].ToString() != "")
                    {
                        seat.reservationId = int.Parse(dbReader["ReservationId"].ToString());
                    }
                    seats.Add(seat);
                }

                Connection.Close();
            }
            catch (NullReferenceException nullexp)
            {
                Console.WriteLine("Error finding Seat: " + nullexp.Message);
                seats = null;
                Connection.Close();
            }
            catch (FaultException fault)
            {
                Console.WriteLine("There is no seat with that reservationID\n\n"+ fault.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error finding Seat: " + e.Message);
                seats = null;
                Connection.Close();
            }
            return seats;
        }

        public void updateSeat(Seat seat)
        {
            try
            {
                Connection.dbconn.Open();
                string insertWithId = "";
                if (seat.reservationId > 0)
                {
                    insertWithId = @"update seat set ReservationID = '" + seat.reservationId + "', status = '" + seat.status + "' where seatnumber = '" + seat.seatNumber + "' and showid ='" + seat.showId + "'";
                }
                else
                    insertWithId = @"update seat set status = '" + seat.status + "' where seatnumber = '" + seat.seatNumber + "' and showid ='" + seat.showId + "'";
                
                cmd = Connection.GetDBCommand(insertWithId);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl i opdatering af seat. Fejl: " + e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }
    }
}
